﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESDispatchBusiness
    {
        #region 更新派工单状态
        public Boolean ChangeDispatchStatus(string strID, int intStatus)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("UPDATE dispatchinfo SET status = {1} WHERE id = '{0}'", strID, intStatus));
            OracleHelper.ExecuteSql(strSQL.ToString());
            return true;
        }
        #endregion

        #region 接收任务
        public Boolean ReceiveTask(string strID, Dictionary<string,string> para)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("UPDATE dispatchinfo");
            strSql.AppendLine(string.Format("SET receivedate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["ReceiveDate"]));
            strSql.AppendLine(string.Format("receiveemployeeid = '{0}',", para["ReceiveEmployeeID"]));
            strSql.AppendLine(string.Format("status = {0}", para["Status"]));
            strSql.AppendLine(string.Format("WHERE id = '{0}'", strID));

            OracleHelper.ExecuteSql(strSql.ToString());

            return true;
        }
        #endregion

        #region 获取派工记录的加工人员
        public DataTable GetEmployeeByDispatchID(string strDispatchInfoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT dei.employeeid,e.employeename,e.fullname");
            strQuery.AppendLine("FROM dispatchemployeeinfo dei");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = dei.employeeid");
            strQuery.AppendLine(string.Format("WHERE dei.dispatchinfoid = '{0}'", strDispatchInfoID));

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取班组派工记录未指派的产品序号
        public DataTable GetWillDispatchProductNo(string strDispatchInfoID)
        {
            DataTable dtProductNo = GetProductNoByDispatchID(strDispatchInfoID);
            DataTable dtDispatchedProductNo = GetDispatchedProductNo(strDispatchInfoID);

            DataTable DT = dtProductNo.Clone();
            foreach(DataRow row in dtProductNo.Rows)
            {
                string strContainerID = row["ContainerID"].ToString();

                Boolean isDispatched = false;
                foreach(DataRow r in dtDispatchedProductNo.Rows)
                {
                    string strID = r["ContainerID"].ToString();

                    if(strContainerID == strID)
                    {
                        isDispatched = true;
                        break;
                    }
                }

                if(isDispatched == false)
                {
                    DT.Rows.Add(row.ItemArray);
                }
            }

            return DT;
        }
        #endregion

        #region 获取班组派工记录的产品序号
        public DataTable GetProductNoByDispatchID(string strDispatchInfoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT dpi.containerid,dpi.containername,dpi.productno");
            strQuery.AppendLine("FROM dispatchproductnoinfo dpi");
            strQuery.AppendLine(string.Format("WHERE dpi.dispatchinfoid = '{0}'", strDispatchInfoID));

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取班组派工记录已指派的产品序号
        public DataTable GetDispatchedProductNo(string strDispatchInfoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT dpi.containerid,dpi.containername,dpi.productno");
            strQuery.AppendLine("FROM dispatchproductnoinfo dpi");
            strQuery.AppendLine("LEFT JOIN dispatchinfo di ON di.id = dpi.dispatchinfoid");
            strQuery.AppendLine(string.Format("WHERE di.parentid = '{0}'", strDispatchInfoID));

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取班组派工记录已指派的数量
        public int GetDispatchedQty(string strDispatchInfoID)
        {
            string strQuery = string.Format("SELECT NVL(SUM(di.qty),0) AS qty FROM dispatchinfo di WHERE di.parentid = '{0}'", strDispatchInfoID);

            DataTable Dt = OracleHelper.GetDataTable(strQuery);
            return Convert.ToInt32(Dt.Rows[0][0]);
        }
        #endregion

        #region 获取资源已派工任务
        public DataTable GetResourceDispatchInfo(string strResourceID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT mo.processno,c.containername,pb.productname,p.description,di.qty,sb.specname,di.plannedcompletiondate");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine(string.Format("WHERE di.resourceid = '{0}'", strResourceID));
            strQuery.AppendLine("ORDER BY di.dispatchdate DESC");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 删除派工记录
        public Boolean DeleteDispatchInfo(string strID)
        {
            ArrayList SQLStringList = new ArrayList();

            //删除主表
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE dispatchinfo WHERE id = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //删除产品序号
            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE dispatchproductnoinfo WHERE dispatchinfoid = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //删除加工人员
            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE dispatchemployeeinfo WHERE dispatchinfoid = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 更新派工记录
        public Boolean UpdateDispatchInfo(string strID, Dictionary<string, string> para, DataTable dtProductNo, DataTable dtEmployee)
        {
            ArrayList SQLStringList = new ArrayList();

            int intCount = 0;
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE dispatchinfo SET");

            if (para.Keys.Contains("DispatchInfoName"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("DispatchInfoName = '{0}'", para["DispatchInfoName"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ContainerID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ContainerID = '{0}'", para["ContainerID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("SpecID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("SpecID = '{0}'", para["SpecID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("DispatchEmployeeID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("DispatchEmployeeID = '{0}'", para["DispatchEmployeeID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("DispatchDate"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("DispatchDate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["DispatchDate"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("DispatchType"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("DispatchType = {0}", para["DispatchType"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("DispatchToType"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("DispatchToType = {0}", para["DispatchToType"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ContainerPhaseID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ContainerPhaseID = '{0}'", para["ContainerPhaseID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("PlannedStartDate"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("PlannedStartDate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["PlannedStartDate"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("PlannedCompletionDate"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("PlannedCompletionDate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["PlannedCompletionDate"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("Qty"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("Qty = {0}", para["Qty"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("UOMID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("UOMID = '{0}'", para["UOMID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ResourceID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ResourceID = '{0}'", para["ResourceID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("Status"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("Status = {0}", para["Status"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ParentID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ParentID = '{0}'", para["ParentID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("WorkflowID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("WorkflowID = '{0}'", para["WorkflowID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("TeamID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("TeamID = '{0}'", para["TeamID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }

            strSQL.AppendLine(string.Format("WHERE id = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //产品序号
            OracleHelper.ExecuteSql(string.Format("DELETE dispatchproductnoinfo WHERE dispatchinfoid = '{0}'", strID));
            if (dtProductNo.Rows.Count > 0)
            {
                foreach (DataRow row in dtProductNo.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO dispatchproductnoinfo(ID,dispatchinfoid,containerid,containername,productno,notes)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["ContainerID"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["ContainerName"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["ProductNo"]));
                    strSQL.AppendLine(string.Format("'{0}'", ""));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            //加工人员
            OracleHelper.ExecuteSql(string.Format("DELETE dispatchemployeeinfo WHERE dispatchinfoid = '{0}'", strID));
            if (dtEmployee.Rows.Count > 0)
            {
                foreach (DataRow row in dtEmployee.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO dispatchemployeeinfo(ID,dispatchinfoid,employeeid,notes)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["EmployeeID"]));
                    strSQL.AppendLine(string.Format("'{0}'", ""));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 获取派工任务
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT mo.processno,mo.oprno,c.containername,pb.productname,p.description,");
            strQuery.AppendLine("   di.qty,sb.specname,t.teamname,di.plannedcompletiondate,di.workflowstepid,");
            strQuery.AppendLine("   di.id,di.teamid,c.qty AS contaiernqty,c.containerid,di.workflowid,di.specid,di.parentid,");
            strQuery.AppendLine("   c.plannedstartdate AS containerstartdate,c.plannedcompletiondate AS containercompletiondate,");
            strQuery.AppendLine("   r.resourcename,di.resourceid,di1.qty AS parentqty,c.productid,mo.mfgordername,");
            strQuery.AppendLine("   di.totalgs,di.SpecSequence,di.ResourceGroupID,di.IsAPS,di.state,ws.sequence, ");
            strQuery.AppendLine("   s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime,p.PartCharacter,c.splitfromid,p.technicalconditions,p.materialguige,p.materialpaihao,p.materialname,");
            strQuery.AppendLine(" nvl( ut.attributevalue,p.technicalconditions||'-'||p.materialpaihao||'-'||p.materialguige) RawMaterial,ut2.attributevalue replacematerialname,ut3.attributevalue replacematerialqty");
            strQuery.AppendLine("FROM dispatchinfo di");  
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("left join currentstatus cu on cu.currentstatusid=c.currentstatusid");
            strQuery.AppendLine("left join workflowstep cuws on cuws.workflowstepid=cu.workflowstepid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = di.resourceid");
            strQuery.AppendLine("LEFT JOIN dispatchinfo di1 ON di1.id = di.parentid");
            strQuery.AppendLine("left join workflowstep ws on ws.workflowstepid=di.workflowstepid ");//add:wangjh 20200921 ws.workflowid=di.workflowid and (ws.specid=s.specid or ws.specbaseid=s.specbaseid)
            strQuery.AppendLine(@"left join userattribute ut on ut.parentid=c.containerid and ut.userattributename='代料规格型号'
left join userattribute ut2 on ut2.parentid=c.containerid and ut2.userattributename='代料名称'
left join userattribute ut3 on ut3.parentid=c.containerid and ut3.userattributename='代料数'");
            strQuery.AppendLine("WHERE c.status = 1 AND di.state = 1  and cuws.workflowid=di.workflowid ");//add:Wangjh 增加只能查当前工艺条件 20201126
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");

            //过滤外协工序
            strQuery.AppendLine(@" and not EXISTS(
select 1 from synergicinfo st
where st.isuse=0 and st.containerid=di.containerid and st.workflowid=di.workflowid and di.specsequence between st.sequence and st.returnsequence
) ");

            if (para.Keys.Contains("Sequence")&& !string.IsNullOrEmpty(para["Sequence"]))//add;Wangjh 20200925
            {
                strQuery.AppendLine(string.Format("AND ws.Sequence = {0}", para["Sequence"]));
            }

            if (para.Keys.Contains("CurrentStepID") && !string.IsNullOrEmpty(para["CurrentStepID"]))//add;Wangjh 20201116
            {
                strQuery.AppendLine(" and cu.workflowstepid=di.workflowstepid ");
            }

            if (para.Keys.Contains("ReportEmployeeID") && !string.IsNullOrEmpty(para["ReportEmployeeID"]))//add;Wangjh 20201116 包含工人ID
            {
                strQuery.AppendFormat(" and exists (select 1 from dispatchemployeeinfo de where de.dispatchinfoid=di.id and de.employeeid='{0}') ",para["ReportEmployeeID"]);
            }

            if (para.Keys.Contains("ReceiveEmployeeID") && !string.IsNullOrEmpty(para["ReceiveEmployeeID"]))//add;Wangjh 20201124
            {
                strQuery.AppendLine(string.Format("AND di.ReceiveEmployeeID = '{0}'", para["ReceiveEmployeeID"]));
            }

            if (para.Keys.Contains("DispatchType"))
            {
                if (!string.IsNullOrEmpty(para["DispatchType"]))
                {
                    strQuery.AppendLine(string.Format("AND di.dispatchtype = {0}", para["DispatchType"]));
                    //strQuery.AppendLine(string.Format("AND di.dispatchtotype = {0}", para["DispatchType"]));
                }
            }

            if (para.Keys.Contains("FactoryID"))
            {
                if (!string.IsNullOrEmpty(para["FactoryID"]))
                {
                    //strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("TeamID"))
            {
                if (!string.IsNullOrEmpty(para["TeamID"]))
                {
                    strQuery.AppendLine(string.Format("AND di.teamid = '{0}'", para["TeamID"]));
                }
            }
            if (para.Keys.Contains("Status"))
            {
                if (!string.IsNullOrEmpty(para["Status"]))
                {
                    strQuery.AppendLine(string.Format("AND di.status IN ({0})", para["Status"]));
                }
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sb.specname) LIKE '%{0}%'", para["SpecName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine("ORDER BY di.dispatchdate DESC");

            return strQuery.ToString();
        }
        #endregion
        #endregion

        #region 物料申请查询
        protected string GetMaterialAppSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT mo.processno,mo.oprno,c.containername,pb.productname,p.description,");
            strQuery.AppendLine("   di.qty,sb.specname,t.teamname,di.plannedcompletiondate,di.workflowstepid,");
            strQuery.AppendLine("   di.id,di.teamid,c.qty AS contaiernqty,c.containerid,di.workflowid,di.specid,di.parentid,");
            strQuery.AppendLine("   c.plannedstartdate AS containerstartdate,c.plannedcompletiondate AS containercompletiondate,");
            strQuery.AppendLine("   r.resourcename,di.resourceid,di1.qty AS parentqty,c.productid,mo.mfgordername,");
            strQuery.AppendLine("   di.totalgs,di.SpecSequence,di.ResourceGroupID,di.IsAPS,di.state,ws.sequence, ");
            strQuery.AppendLine("   s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime,p.PartCharacter,c.splitfromid,p.technicalconditions,p.materialguige,p.materialpaihao,p.materialname,");
            strQuery.AppendLine(" nvl( ut.attributevalue,p.technicalconditions||'-'||p.materialpaihao||'-'||p.materialguige) RawMaterial,ut2.attributevalue replacematerialname,ut3.attributevalue replacematerialqty");
            strQuery.AppendLine(",ma.erpreturnmsg,ma.issenderp ");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("left join currentstatus cu on cu.currentstatusid=c.currentstatusid");
            strQuery.AppendLine("left join workflowstep cuws on cuws.workflowstepid=cu.workflowstepid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = di.resourceid");
            strQuery.AppendLine("LEFT JOIN dispatchinfo di1 ON di1.id = di.parentid");
            strQuery.AppendLine("left join workflowstep ws on ws.workflowstepid=di.workflowstepid ");//add:wangjh 20200921 ws.workflowid=di.workflowid and (ws.specid=s.specid or ws.specbaseid=s.specbaseid)
            strQuery.AppendLine(@"left join userattribute ut on ut.parentid=c.containerid and ut.userattributename='代料规格型号'
left join userattribute ut2 on ut2.parentid=c.containerid and ut2.userattributename='代料名称'
left join userattribute ut3 on ut3.parentid=c.containerid and ut3.userattributename='代料数'");
            strQuery.AppendLine(" left join materialappinfo ma on ma.dispatchinfoid=di.id ");
            strQuery.AppendLine("WHERE c.status = 1 AND di.state = 1  and cuws.workflowid=di.workflowid ");//add:Wangjh 增加只能查当前工艺条件 20201126
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");
            strQuery.AppendLine(" and (ma.id is null or ma.issenderp not in (0,5)) ");
            //过滤外协工序
            strQuery.AppendLine(@" and not EXISTS(
select 1 from synergicinfo st
where st.isuse=0 and st.containerid=di.containerid and st.workflowid=di.workflowid and di.specsequence between st.sequence and st.returnsequence
) ");
            if (para.Keys.Contains("RawMaterial") && !string.IsNullOrEmpty(para["RawMaterial"]))//add;Wangjh 20210803
            {
                strQuery.AppendLine(string.Format("AND nvl( ut.attributevalue,p.technicalconditions||'-'||p.materialpaihao||'-'||p.materialguige) like '%{0}%' ", para["RawMaterial"]));
            }

            if (para.Keys.Contains("Sequence") && !string.IsNullOrEmpty(para["Sequence"]))//add;Wangjh 20200925
            {
                strQuery.AppendLine(string.Format("AND ws.Sequence = {0}", para["Sequence"]));
            }

            if (para.Keys.Contains("Sequence") && !string.IsNullOrEmpty(para["Sequence"]))//add;Wangjh 20200925
            {
                strQuery.AppendLine(string.Format("AND ws.Sequence = {0}", para["Sequence"]));
            }

            if (para.Keys.Contains("CurrentStepID") && !string.IsNullOrEmpty(para["CurrentStepID"]))//add;Wangjh 20201116
            {
                strQuery.AppendLine(" and cu.workflowstepid=di.workflowstepid ");
            }

            if (para.Keys.Contains("ReportEmployeeID") && !string.IsNullOrEmpty(para["ReportEmployeeID"]))//add;Wangjh 20201116 包含工人ID
            {
                strQuery.AppendFormat(" and exists (select 1 from dispatchemployeeinfo de where de.dispatchinfoid=di.id and de.employeeid='{0}') ", para["ReportEmployeeID"]);
            }

            if (para.Keys.Contains("ReceiveEmployeeID") && !string.IsNullOrEmpty(para["ReceiveEmployeeID"]))//add;Wangjh 20201124
            {
                strQuery.AppendLine(string.Format("AND di.ReceiveEmployeeID = '{0}'", para["ReceiveEmployeeID"]));
            }

            if (para.Keys.Contains("DispatchType"))
            {
                if (!string.IsNullOrEmpty(para["DispatchType"]))
                {
                    strQuery.AppendLine(string.Format("AND di.dispatchtype = {0}", para["DispatchType"]));
                    //strQuery.AppendLine(string.Format("AND di.dispatchtotype = {0}", para["DispatchType"]));
                }
            }

            if (para.Keys.Contains("FactoryID"))
            {
                if (!string.IsNullOrEmpty(para["FactoryID"]))
                {
                    //strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("TeamID"))
            {
                if (!string.IsNullOrEmpty(para["TeamID"]))
                {
                    strQuery.AppendLine(string.Format("AND di.teamid = '{0}'", para["TeamID"]));
                }
            }
            if (para.Keys.Contains("Status"))
            {
                if (!string.IsNullOrEmpty(para["Status"]))
                {
                    strQuery.AppendLine(string.Format("AND di.status IN ({0})", para["Status"]));
                }
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sb.specname) LIKE '%{0}%'", para["SpecName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine("ORDER BY di.dispatchdate DESC");

            return strQuery.ToString();
        }

        public uMESPagingDataDTO GetMaterialAppData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetMaterialAppSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 获取班组已派工任务
        public DataTable GetTeamDispatchInfo(string strTeamID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT mo.processno,c.containername,pb.productname,p.description,di.qty,sb.specname,di.plannedcompletiondate");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine(string.Format("WHERE di.teamid = '{0}'", strTeamID));
            strQuery.AppendLine("ORDER BY di.dispatchdate DESC");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 添加派工记录
        /// <summary>
        /// 添加派工记录
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public Boolean AddDispatchInfo(Dictionary<string,string> para, DataTable dtProductNo, DataTable dtEmployee)
        {
            ArrayList SQLStringList = new ArrayList();

            //主表
            string strID = Guid.NewGuid().ToString();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO dispatchinfo(Id, dispatchinfoname, containerid, specid, dispatchemployeeid, dispatchdate,");
            strSQL.AppendLine("       dispatchtype, dispatchtotype, containerphaseid, plannedstartdate, plannedcompletiondate, qty,");
            strSQL.AppendLine("       uomid, resourceid, status, parentid, workflowid, teamid, totalgs, specsequence, resourcegroupid, isaps, workflowstepid, state)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["DispatchInfoName"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["DispatchEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["DispatchDate"]));
            strSQL.AppendLine(string.Format("{0},", para["DispatchType"]));
            strSQL.AppendLine(string.Format("{0},", para["DispatchToType"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerPhaseID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["PlannedStartDate"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["PlannedCompletionDate"]));
            strSQL.AppendLine(string.Format("{0},", para["Qty"]));
            strSQL.AppendLine(string.Format("'{0}',", para["UOMID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ResourceID"]));
            strSQL.AppendLine(string.Format("{0},", para["Status"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ParentID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["WorkflowID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["TeamID"]));
            strSQL.AppendLine(string.Format("{0},", para["TotalGS"]));
            strSQL.AppendLine(string.Format("{0},", para["SpecSequence"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ResourceGroupID"]));
            strSQL.AppendLine(string.Format("{0},", para["IsAPS"]));
            strSQL.AppendLine(string.Format("'{0}',", para["WorkflowStepID"]));
            strSQL.AppendLine(string.Format("{0}", para["State"]));
            strSQL.AppendLine(")");

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //产品序号
            if (dtProductNo.Rows.Count > 0)
            {
                foreach (DataRow row in dtProductNo.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO dispatchproductnoinfo(ID,dispatchinfoid,containerid,containername,productno,notes)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["ContainerID"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["ContainerName"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["ProductNo"]));
                    strSQL.AppendLine(string.Format("'{0}'", ""));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            //加工人员
            if (dtEmployee.Rows.Count > 0)
            {
                foreach (DataRow row in dtEmployee.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO dispatchemployeeinfo(ID,dispatchinfoid,employeeid,notes)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["EmployeeID"]));
                    strSQL.AppendLine(string.Format("'{0}'", ""));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 获取已派工信息
        /// <summary>
        /// 获取已派工信息
        /// </summary>
        /// <param name="strContainerID"></param>
        /// <param name="strWorkflowID"></param>
        /// <returns></returns>
        public DataTable GetDispatchInfo(string strContainerID, string strWorkflowID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.specid,di.teamid,di.plannedcompletiondate");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendFormat("WHERE di.containerid = '{0}'", strContainerID);
            strQuery.AppendFormat("AND di.workflowid = '{0}'", strWorkflowID);
            return OracleHelper.GetDataTable(strQuery.ToString());
        }

        public DataTable GetDispatchInfoNew(string strContainerID, string strWorkflowID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.containerid,di.workflowid, di.specid,di.teamid,di.plannedcompletiondate,di.dispatchtype,di.dispatchtotype");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendFormat("WHERE di.containerid = '{0}'", strContainerID);
            strQuery.AppendFormat("AND di.workflowid = '{0}'", strWorkflowID);
            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion
    }
}
